/*
 * 人力资源与项目管理系统数据库初始化脚本（优化版）
 * 创建日期：2025-10-08
 * 数据库：PostgreSQL 13+
 * 描述：此脚本完整初始化人力资源与项目管理系统，采用模块化设计和幂等性处理
 * 执行说明：
 *   1. 使用PostgreSQL超级用户连接至默认数据库（如postgres）
 *   2. 执行此脚本的第一部分（至创建数据库完毕）
 *   3. 手动连接到新数据库：\c hr_project_db
 *   4. 继续执行此脚本的剩余部分
 */

-- ==================== 第一部分：数据库和用户创建 ====================
-- 使用DO块确保幂等性执行
DO $$
BEGIN
    -- 1.1 创建专属管理用户（密码应通过环境变量传入）
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'hr_admin') THEN
        CREATE ROLE hr_admin WITH LOGIN PASSWORD 'your_secure_password_here' CREATEDB;
        RAISE NOTICE '用户 hr_admin 创建成功';
    ELSE
        RAISE NOTICE '用户 hr_admin 已存在，跳过创建';
    END IF;

    -- 1.2 创建专属数据库
    IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'hr_project_db') THEN
        CREATE DATABASE hr_project_db
        WITH
        OWNER = hr_admin
        ENCODING = 'UTF8'
        LC_COLLATE = 'zh_CN.UTF-8'
        LC_CTYPE = 'zh_CN.UTF-8'
        TEMPLATE = template0
        CONNECTION LIMIT = 50;
        RAISE NOTICE '数据库 hr_project_db 创建成功';
    ELSE
        RAISE NOTICE '数据库 hr_project_db 已存在，跳过创建';
    END IF;
END
$$;

COMMENT ON ROLE hr_admin IS '人力资源与项目管理系统数据库管理员';
COMMENT ON DATABASE hr_project_db IS '人力资源与项目管理系统主数据库';

-- ！！！重要：执行完以上语句后，请连接到 hr_project_db 数据库再继续执行以下部分！！！
-- 在 psql 中使用命令：\c hr_project_db

-- ==================== 第二部分：模式创建与配置 ====================
-- 连接到hr_project_db后执行以下部分

-- 2.1 创建模式（Schemas）以进行逻辑隔离
DO $$
BEGIN
    -- 创建核心人事模式
    CREATE SCHEMA IF NOT EXISTS hr_core;
    COMMENT ON SCHEMA hr_core IS '存储核心人事与组织架构数据';

    -- 创建项目管理模式
    CREATE SCHEMA IF NOT EXISTS project_management;
    COMMENT ON SCHEMA project_management IS '存储项目、任务及分配相关数据';

    -- 创建技能库存模式
    CREATE SCHEMA IF NOT EXISTS skills_inventory;
    COMMENT ON SCHEMA skills_inventory IS '存储技能字典及员工技能关联数据';

    RAISE NOTICE '所有模式创建完成';
END
$$;

-- 设置默认搜索路径
ALTER DATABASE hr_project_db SET search_path TO hr_core, project_management, skills_inventory, public;

-- ==================== 第三部分：表结构创建 ====================

-- 3.1 部门表[1](@ref)
CREATE TABLE IF NOT EXISTS hr_core.departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    parent_department_id INTEGER NULL,
    manager_id INTEGER NULL,
    budget DECIMAL(15,2) CHECK (budget >= 0),
    created_date DATE DEFAULT CURRENT_DATE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE hr_core.departments IS '存储公司的部门组织结构信息，支持层级关系';
COMMENT ON COLUMN hr_core.departments.department_id IS '部门唯一标识符，主键，自增长';
COMMENT ON COLUMN hr_core.departments.department_name IS '部门完整名称，不可为空';
COMMENT ON COLUMN hr_core.departments.parent_department_id IS '父部门ID，用于构建部门层级树，空值表示根级部门';
COMMENT ON COLUMN hr_core.departments.manager_id IS '部门负责人ID，关联员工表';
COMMENT ON COLUMN hr_core.departments.budget IS '部门年度预算总额，必须大于等于0';
COMMENT ON COLUMN hr_core.departments.created_date IS '部门创建日期';
COMMENT ON COLUMN hr_core.departments.created_at IS '记录创建时间';
COMMENT ON COLUMN hr_core.departments.updated_at IS '记录最后更新时间';

-- 3.2 员工表
CREATE TABLE IF NOT EXISTS hr_core.employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL CHECK (length(first_name) >= 1),
    last_name VARCHAR(50) NOT NULL CHECK (length(last_name) >= 1),
    email VARCHAR(100) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$'),
    hire_date DATE NOT NULL CHECK (hire_date <= CURRENT_DATE),
    department_id INTEGER NULL,
    manager_id INTEGER NULL,
    employment_type VARCHAR(20) DEFAULT '全职' CHECK (employment_type IN ('全职', '兼职', '合同')),
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE hr_core.employees IS '存储企业所有员工的基本人事信息';
COMMENT ON COLUMN hr_core.employees.employee_id IS '员工唯一标识符，主键，自增长';
COMMENT ON COLUMN hr_core.employees.first_name IS '员工名字，长度至少1个字符';
COMMENT ON COLUMN hr_core.employees.last_name IS '员工姓氏，长度至少1个字符';
COMMENT ON COLUMN hr_core.employees.email IS '企业邮箱地址，用于登录和通信，必须符合邮箱格式';
COMMENT ON COLUMN hr_core.employees.hire_date IS '员工入职日期，不能晚于当前日期';
COMMENT ON COLUMN hr_core.employees.department_id IS '员工所属部门ID';
COMMENT ON COLUMN hr_core.employees.manager_id IS '员工的直属经理ID';
COMMENT ON COLUMN hr_core.employees.employment_type IS '雇佣类型：全职、兼职、合同工';
COMMENT ON COLUMN hr_core.employees.active IS '标识员工是否在职（true-在职，false-已离职）';
COMMENT ON COLUMN hr_core.employees.created_at IS '记录创建时间';
COMMENT ON COLUMN hr_core.employees.updated_at IS '记录最后更新时间';

-- 3.3 添加外键约束（解决循环依赖问题）
DO $$
BEGIN
    -- 添加部门表的外键约束
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_departments_manager' AND table_name = 'departments'
    ) THEN
        ALTER TABLE hr_core.departments
            ADD CONSTRAINT fk_departments_parent
            FOREIGN KEY (parent_department_id) REFERENCES hr_core.departments(department_id),
            ADD CONSTRAINT fk_departments_manager
            FOREIGN KEY (manager_id) REFERENCES hr_core.employees(employee_id) DEFERRABLE INITIALLY DEFERRED;
    END IF;

    -- 添加员工表的外键约束
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_employees_department' AND table_name = 'employees'
    ) THEN
        ALTER TABLE hr_core.employees
            ADD CONSTRAINT fk_employees_department
            FOREIGN KEY (department_id) REFERENCES hr_core.departments(department_id),
            ADD CONSTRAINT fk_employees_manager
            FOREIGN KEY (manager_id) REFERENCES hr_core.employees(employee_id);
    END IF;
END
$$;

-- 3.4 薪资历史表
CREATE TABLE IF NOT EXISTS hr_core.salaries (
    salary_id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL,
    salary_amount DECIMAL(10,2) NOT NULL CHECK (salary_amount > 0),
    effective_date DATE NOT NULL,
    end_date DATE CHECK (end_date IS NULL OR end_date > effective_date),
    salary_type VARCHAR(20) DEFAULT '月薪' CHECK (salary_type IN ('月薪', '时薪', '年薪', '项目提成')),
    change_reason VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE hr_core.salaries IS '记录员工的薪资变动历史，支持薪资审计和分析';
COMMENT ON COLUMN hr_core.salaries.salary_id IS '薪资记录唯一标识符，主键，自增长';
COMMENT ON COLUMN hr_core.salaries.employee_id IS '关联的员工ID';
COMMENT ON COLUMN hr_core.salaries.salary_amount IS '薪资数额，必须大于0';
COMMENT ON COLUMN hr_core.salaries.effective_date IS '薪资生效日期';
COMMENT ON COLUMN hr_core.salaries.end_date IS '薪资失效日期，空值表示当前有效薪资，必须晚于生效日期';
COMMENT ON COLUMN hr_core.salaries.salary_type IS '薪资类型：月薪、时薪、年薪或项目提成';
COMMENT ON COLUMN hr_core.salaries.change_reason IS '薪资变动原因（如：年度调薪，晋升调薪，绩效调薪）';
COMMENT ON COLUMN hr_core.salaries.created_at IS '记录创建时间';

-- 3.5 添加薪资表外键约束
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_salaries_employee' AND table_name = 'salaries'
    ) THEN
        ALTER TABLE hr_core.salaries
            ADD CONSTRAINT fk_salaries_employee
            FOREIGN KEY (employee_id) REFERENCES hr_core.employees(employee_id) ON DELETE CASCADE;
    END IF;
END
$$;

-- 3.6 项目表
CREATE TABLE IF NOT EXISTS project_management.projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL CHECK (length(project_name) >= 2),
    description TEXT,
    department_id INTEGER NULL,
    budget DECIMAL(15,2) CHECK (budget >= 0),
    start_date DATE,
    end_date DATE CHECK (end_date IS NULL OR end_date >= start_date),
    status VARCHAR(20) DEFAULT '规划中' CHECK (status IN ('规划中', '进行中', '暂停', '已完成', '取消')),
    priority VARCHAR(10) DEFAULT '中' CHECK (priority IN ('低', '中', '高', '紧急')),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE project_management.projects IS '存储所有项目的基本信息和生命周期状态';
COMMENT ON COLUMN project_management.projects.project_id IS '项目唯一标识符，主键，自增长';
COMMENT ON COLUMN project_management.projects.project_name IS '项目名称，长度至少2个字符';
COMMENT ON COLUMN project_management.projects.description IS '项目详细描述和目标';
COMMENT ON COLUMN project_management.projects.department_id IS '项目负责部门或归属部门ID';
COMMENT ON COLUMN project_management.projects.budget IS '项目总预算，必须大于等于0';
COMMENT ON COLUMN project_management.projects.start_date IS '项目计划开始日期';
COMMENT ON COLUMN project_management.projects.end_date IS '项目计划结束日期，必须晚于或等于开始日期';
COMMENT ON COLUMN project_management.projects.status IS '项目当前状态';
COMMENT ON COLUMN project_management.projects.priority IS '项目优先级';
COMMENT ON COLUMN project_management.projects.created_at IS '项目创建时间';
COMMENT ON COLUMN project_management.projects.updated_at IS '项目信息最后更新时间';

-- 3.7 添加项目表外键约束
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_projects_department' AND table_name = 'projects'
    ) THEN
        ALTER TABLE project_management.projects
            ADD CONSTRAINT fk_projects_department
            FOREIGN KEY (department_id) REFERENCES hr_core.departments(department_id);
    END IF;
END
$$;

-- 3.8 项目任务表
CREATE TABLE IF NOT EXISTS project_management.project_tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL,
    task_name VARCHAR(200) NOT NULL CHECK (length(task_name) >= 2),
    task_description TEXT,
    parent_task_id INTEGER NULL,
    assigned_to INTEGER NULL,
    estimated_hours DECIMAL(6,2) CHECK (estimated_hours > 0),
    actual_hours DECIMAL(5,2) CHECK (actual_hours >= 0),
    priority INTEGER DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
    status VARCHAR(20) DEFAULT '未开始' CHECK (status IN ('未开始', '进行中', '已完成', '阻塞', '取消')),
    start_date DATE,
    end_date DATE CHECK (end_date IS NULL OR end_date >= start_date),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE project_management.project_tasks IS '存储项目的具体任务信息，支持多级任务分解（WBS结构）';
COMMENT ON COLUMN project_management.project_tasks.task_id IS '任务唯一标识符，主键，自增长';
COMMENT ON COLUMN project_management.project_tasks.project_id IS '所属项目ID';
COMMENT ON COLUMN project_management.project_tasks.task_name IS '任务名称，长度至少2个字符';
COMMENT ON COLUMN project_management.project_tasks.task_description IS '任务详细描述和要求';
COMMENT ON COLUMN project_management.project_tasks.parent_task_id IS '父任务ID，用于构建多级任务结构，空值表示根级任务';
COMMENT ON COLUMN project_management.project_tasks.assigned_to IS '任务负责人ID';
COMMENT ON COLUMN project_management.project_tasks.estimated_hours IS '任务预估工时（小时），必须大于0';
COMMENT ON COLUMN project_management.project_tasks.actual_hours IS '任务实际耗时（小时），必须大于等于0';
COMMENT ON COLUMN project_management.project_tasks.priority IS '任务优先级（1-最高，5-最低）';
COMMENT ON COLUMN project_management.project_tasks.status IS '任务当前状态';
COMMENT ON COLUMN project_management.project_tasks.start_date IS '任务计划开始日期';
COMMENT ON COLUMN project_management.project_tasks.end_date IS '任务计划结束日期，必须晚于或等于开始日期';
COMMENT ON COLUMN project_management.project_tasks.created_at IS '任务创建时间';
COMMENT ON COLUMN project_management.project_tasks.updated_at IS '任务信息最后更新时间';

-- 3.9 添加任务表外键约束
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_tasks_project' AND table_name = 'project_tasks'
    ) THEN
        ALTER TABLE project_management.project_tasks
            ADD CONSTRAINT fk_tasks_project
            FOREIGN KEY (project_id) REFERENCES project_management.projects(project_id) ON DELETE CASCADE,
            ADD CONSTRAINT fk_tasks_parent
            FOREIGN KEY (parent_task_id) REFERENCES project_management.project_tasks(task_id),
            ADD CONSTRAINT fk_tasks_assigned
            FOREIGN KEY (assigned_to) REFERENCES hr_core.employees(employee_id);
    END IF;
END
$$;

-- 3.10 项目分配表
CREATE TABLE IF NOT EXISTS project_management.project_assignments (
    assignment_id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL,
    project_id INTEGER NOT NULL,
    role VARCHAR(50) NOT NULL,
    allocation_percentage INTEGER NOT NULL CHECK (allocation_percentage BETWEEN 0 AND 100),
    start_date DATE NOT NULL,
    end_date DATE CHECK (end_date IS NULL OR end_date >= start_date),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE project_management.project_assignments IS '记录员工在项目中的分配情况、角色和时间投入比例';
COMMENT ON COLUMN project_management.project_assignments.assignment_id IS '分配记录唯一标识符，主键，自增长';
COMMENT ON COLUMN project_management.project_assignments.employee_id IS '被分配的员工ID';
COMMENT ON COLUMN project_management.project_assignments.project_id IS '被分配的项目ID';
COMMENT ON COLUMN project_management.project_assignments.role IS '员工在项目中的角色（如：开发工程师，项目经理，测试工程师）';
COMMENT ON COLUMN project_management.project_assignments.allocation_percentage IS '员工在该项目上的时间投入百分比，取值范围0-100';
COMMENT ON COLUMN project_management.project_assignments.start_date IS '分配生效日期';
COMMENT ON COLUMN project_management.project_assignments.end_date IS '分配结束日期，空值表示长期有效，必须晚于或等于开始日期';
COMMENT ON COLUMN project_management.project_assignments.created_at IS '记录创建时间';

-- 3.11 添加项目分配表外键约束
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_assignments_employee' AND table_name = 'project_assignments'
    ) THEN
        ALTER TABLE project_management.project_assignments
            ADD CONSTRAINT fk_assignments_employee
            FOREIGN KEY (employee_id) REFERENCES hr_core.employees(employee_id) ON DELETE CASCADE,
            ADD CONSTRAINT fk_assignments_project
            FOREIGN KEY (project_id) REFERENCES project_management.projects(project_id) ON DELETE CASCADE;
    END IF;
END
$$;

-- 3.12 任务时间日志表
CREATE TABLE IF NOT EXISTS project_management.task_time_logs (
    log_id SERIAL PRIMARY KEY,
    task_id INTEGER NOT NULL,
    employee_id INTEGER NOT NULL,
    log_date DATE NOT NULL CHECK (log_date <= CURRENT_DATE),
    hours_worked DECIMAL(5,2) NOT NULL CHECK (hours_worked > 0 AND hours_worked <= 24),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE project_management.task_time_logs IS '记录员工在具体任务上花费的工作时间，用于工时统计和项目成本核算';
COMMENT ON COLUMN project_management.task_time_logs.log_id IS '时间日志唯一标识符，主键，自增长';
COMMENT ON COLUMN project_management.task_time_logs.task_id IS '关联的任务ID';
COMMENT ON COLUMN project_management.task_time_logs.employee_id IS '记录工时的员工ID';
COMMENT ON COLUMN project_management.task_time_logs.log_date IS '工时记录日期，不能晚于当前日期';
COMMENT ON COLUMN project_management.task_time_logs.hours_worked IS '实际工作小时数，必须大于0且小于等于24';
COMMENT ON COLUMN project_management.task_time_logs.description IS '工作内容描述或成果说明';
COMMENT ON COLUMN project_management.task_time_logs.created_at IS '记录创建时间';

-- 3.13 添加时间日志表外键约束
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_timelogs_task' AND table_name = 'task_time_logs'
    ) THEN
        ALTER TABLE project_management.task_time_logs
            ADD CONSTRAINT fk_timelogs_task
            FOREIGN KEY (task_id) REFERENCES project_management.project_tasks(task_id) ON DELETE CASCADE,
            ADD CONSTRAINT fk_timelogs_employee
            FOREIGN KEY (employee_id) REFERENCES hr_core.employees(employee_id) ON DELETE CASCADE;
    END IF;
END
$$;

-- 3.14 技能表
CREATE TABLE IF NOT EXISTS skills_inventory.skills (
    skill_id SERIAL PRIMARY KEY,
    skill_name VARCHAR(100) NOT NULL UNIQUE CHECK (length(skill_name) >= 2),
    skill_category VARCHAR(50),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE skills_inventory.skills IS '存储公司定义的技能字典，用于标准化技能名称';
COMMENT ON COLUMN skills_inventory.skills.skill_id IS '技能唯一标识符，主键，自增长';
COMMENT ON COLUMN skills_inventory.skills.skill_name IS '技能名称（如：Java编程，项目管理），长度至少2个字符，唯一';
COMMENT ON COLUMN skills_inventory.skills.skill_category IS '技能分类（如：技术技能，软技能，管理技能）';
COMMENT ON COLUMN skills_inventory.skills.description IS '技能详细描述和应用场景';
COMMENT ON COLUMN skills_inventory.skills.created_at IS '记录创建时间';

-- 3.15 员工技能表
CREATE TABLE IF NOT EXISTS skills_inventory.employee_skills (
    employee_id INTEGER NOT NULL,
    skill_id INTEGER NOT NULL,
    proficiency_level INTEGER NOT NULL CHECK (proficiency_level BETWEEN 1 AND 5),
    certified_date DATE CHECK (certified_date <= CURRENT_DATE),
    certification_authority VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (employee_id, skill_id)
);

COMMENT ON TABLE skills_inventory.employee_skills IS '记录员工掌握的技能及其熟练度，解决员工与技能的多对多关系';
COMMENT ON COLUMN skills_inventory.employee_skills.employee_id IS '员工ID，联合主键部分';
COMMENT ON COLUMN skills_inventory.employee_skills.skill_id IS '技能ID，联合主键部分';
COMMENT ON COLUMN skills_inventory.employee_skills.proficiency_level IS '熟练度等级（1-初级，3-中级，5-专家），取值范围1-5';
COMMENT ON COLUMN skills_inventory.employee_skills.certified_date IS '技能认证日期，不能晚于当前日期';
COMMENT ON COLUMN skills_inventory.employee_skills.certification_authority IS '认证机构名称';
COMMENT ON COLUMN skills_inventory.employee_skills.created_at IS '记录创建时间';

-- 3.16 添加员工技能表外键约束
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_employee_skills_employee' AND table_name = 'employee_skills'
    ) THEN
        ALTER TABLE skills_inventory.employee_skills
            ADD CONSTRAINT fk_employee_skills_employee
            FOREIGN KEY (employee_id) REFERENCES hr_core.employees(employee_id) ON DELETE CASCADE,
            ADD CONSTRAINT fk_employee_skills_skill
            FOREIGN KEY (skill_id) REFERENCES skills_inventory.skills(skill_id) ON DELETE CASCADE;
    END IF;
END
$$;

-- ==================== 第四部分：索引创建 ====================

-- 4.1 员工表索引
CREATE INDEX IF NOT EXISTS idx_employees_department ON hr_core.employees(department_id);
CREATE INDEX IF NOT EXISTS idx_employees_manager ON hr_core.employees(manager_id);
CREATE INDEX IF NOT EXISTS idx_employees_email ON hr_core.employees(email);
CREATE INDEX IF NOT EXISTS idx_employees_active ON hr_core.employees(active) WHERE active = true;

-- 4.2 部门表索引
CREATE INDEX IF NOT EXISTS idx_departments_parent ON hr_core.departments(parent_department_id);
CREATE INDEX IF NOT EXISTS idx_departments_manager ON hr_core.departments(manager_id);

-- 4.3 薪资表索引
CREATE INDEX IF NOT EXISTS idx_salaries_employee ON hr_core.salaries(employee_id);
CREATE INDEX IF NOT EXISTS idx_salaries_dates ON hr_core.salaries(effective_date, end_date);
CREATE INDEX IF NOT EXISTS idx_salaries_current ON hr_core.salaries(employee_id) WHERE end_date IS NULL;

-- 4.4 项目表索引
CREATE INDEX IF NOT EXISTS idx_projects_department ON project_management.projects(department_id);
CREATE INDEX IF NOT EXISTS idx_projects_status ON project_management.projects(status);
CREATE INDEX IF NOT EXISTS idx_projects_dates ON project_management.projects(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_projects_priority ON project_management.projects(priority);

-- 4.5 任务表索引
CREATE INDEX IF NOT EXISTS idx_tasks_project ON project_management.project_tasks(project_id);
CREATE INDEX IF NOT EXISTS idx_tasks_assigned ON project_management.project_tasks(assigned_to);
CREATE INDEX IF NOT EXISTS idx_tasks_parent ON project_management.project_tasks(parent_task_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON project_management.project_tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_dates ON project_management.project_tasks(start_date, end_date);

-- 4.6 项目分配表索引
CREATE INDEX IF NOT EXISTS idx_assignments_employee ON project_management.project_assignments(employee_id);
CREATE INDEX IF NOT EXISTS idx_assignments_project ON project_management.project_assignments(project_id);
CREATE INDEX IF NOT EXISTS idx_assignments_dates ON project_management.project_assignments(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_assignments_current ON project_management.project_assignments(employee_id) WHERE end_date IS NULL;

-- 4.7 时间日志表索引
CREATE INDEX IF NOT EXISTS idx_timelogs_task ON project_management.task_time_logs(task_id);
CREATE INDEX IF NOT EXISTS idx_timelogs_employee ON project_management.task_time_logs(employee_id);
CREATE INDEX IF NOT EXISTS idx_timelogs_date ON project_management.task_time_logs(log_date);

-- 4.8 技能表索引
CREATE INDEX IF NOT EXISTS idx_skills_category ON skills_inventory.skills(skill_category);
CREATE INDEX IF NOT EXISTS idx_skills_name ON skills_inventory.skills(skill_name);

-- 4.9 员工技能表索引
CREATE INDEX IF NOT EXISTS idx_employee_skills_skill ON skills_inventory.employee_skills(skill_id);
CREATE INDEX IF NOT EXISTS idx_employee_skills_level ON skills_inventory.employee_skills(proficiency_level);

-- ==================== 第五部分：自动更新触发器 ====================

-- 5.1 创建自动更新触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 5.2 为需要更新时间的表创建触发器
DO $$
BEGIN
    -- 部门表触发器
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_departments_updated_at') THEN
        CREATE TRIGGER update_departments_updated_at
            BEFORE UPDATE ON hr_core.departments
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    END IF;

    -- 员工表触发器
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_employees_updated_at') THEN
        CREATE TRIGGER update_employees_updated_at
            BEFORE UPDATE ON hr_core.employees
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    END IF;

    -- 项目表触发器
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_projects_updated_at') THEN
        CREATE TRIGGER update_projects_updated_at
            BEFORE UPDATE ON project_management.projects
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    END IF;

    -- 任务表触发器
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_tasks_updated_at') THEN
        CREATE TRIGGER update_tasks_updated_at
            BEFORE UPDATE ON project_management.project_tasks
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    END IF;
END
$$;

-- ==================== 第六部分：权限配置 ====================

-- 6.1 授予模式权限[6,7](@ref)
GRANT USAGE ON SCHEMA hr_core TO hr_admin;
GRANT USAGE ON SCHEMA project_management TO hr_admin;
GRANT USAGE ON SCHEMA skills_inventory TO hr_admin;

-- 6.2 授予表操作权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA hr_core TO hr_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA project_management TO hr_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA skills_inventory TO hr_admin;

-- 6.3 授予序列权限
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA hr_core TO hr_admin;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA project_management TO hr_admin;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA skills_inventory TO hr_admin;

-- 6.4 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA hr_core GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hr_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA project_management GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hr_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA skills_inventory GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hr_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA hr_core GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO hr_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA project_management GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO hr_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA skills_inventory GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO hr_admin;

-- ==================== 第七部分：完成提示 ====================

DO $$
BEGIN
    RAISE NOTICE '==================================================';
    RAISE NOTICE '人力资源与项目管理系统数据库初始化完成';
    RAISE NOTICE '已创建用户 hr_admin，数据库 hr_project_db';
    RAISE NOTICE '已创建3个模式，9个数据表及相关索引与权限';
    RAISE NOTICE '请确保在应用中使用连接池并设置合理的连接参数';
    RAISE NOTICE '==================================================';
END
$$;